﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Vegetable.Model;
using OMG.Fill.Vegetable.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace OMG.Fill.Vegetable.Service
{
    /// <summary>
    /// 蔬菜技术推广人员情况表T_VegTecPromotion通用操作类
    /// </summary>
    public class VegTecPromotionServices : VegTecPromotions
    {
        DictServices dict = new DictServices();
        public Pager<List<VegTecPromotionEntity>> GetPageList(VegTecPromotionQuery query)
        {
            Pager<List<VegTecPromotionEntity>> obj = new Pager<List<VegTecPromotionEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_VegTecPromotion where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<VegTecPromotionEntity>(strSql, paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal, paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取蔬菜技术推广人员情况表T_VegTecPromotion分页列表失败", ex);
                return obj;
            }
        }
        /// <summary>
        /// 蔬菜外销情况表----数据填报(数据更新)
        /// </summary>
        /// <returns></returns>
        public ResResult<VegTecPromotionEntity> UpdateModelList(VegTecPromotionEntity model)
        {
            model.UpdateTime = DateTime.Now;
            ResResult<VegTecPromotionEntity> result = new ResResult<VegTecPromotionEntity>();
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"UPDATE dbo.T_VegTecPromotion set ";
            strSql += "ReportId = @ReportId,";
            strSql += "InstituNum = @InstituNum,";
            strSql += "InstituNum_City = @InstituNum_City,";
            strSql += "InstituNum_Area = @InstituNum_Area,";
            strSql += "InstituNum_town = @InstituNum_town,";
            strSql += "PeopleNum = @PeopleNum,";
            strSql += "PeopleNum_City = @PeopleNum_City,";
            strSql += "PeopleNum_Area = @PeopleNum_Area,";
            strSql += "PeopleNum_man = @PeopleNum_man,";
            strSql += "PeopleNum_woman = @PeopleNum_woman,";
            strSql += "PeopleNum_age1 = @PeopleNum_age1,";
            strSql += "PeopleNum_age2 = @PeopleNum_age2,";
            strSql += "PeopleNum_age3 = @PeopleNum_age3,";
            strSql += "PeopleNum_age4 = @PeopleNum_age4,";
            strSql += "PeopleNum_xlss = @PeopleNum_xlss,";
            strSql += "PeopleNum_xlbk = @PeopleNum_xlbk,";
            strSql += "PeopleNum_xldz = @PeopleNum_xldz,";
            strSql += "PeopleNum_xlzz = @PeopleNum_xlzz,";
            strSql += "PeopleNum_xlqt = @PeopleNum_xlqt,";
            strSql += "PeopleNum_zczgj = @PeopleNum_zczgj,";
            strSql += "PeopleNum_zcfgj = @PeopleNum_zcfgj,";
            strSql += "PeopleNum_zczj = @PeopleNum_zczj,";
            strSql += "PeopleNum_zccj = @PeopleNum_zccj,";
            strSql += "PeopleNum_zcqt = @PeopleNum_zcqt,";
            strSql += "AddUser = @AddUser,";
            strSql += "AddTime = @AddTime,";
            strSql += "UpdateUser = @UpdateUser,";
            strSql += "UpdateTime = @UpdateTime";
            strSql += " where Id = @Id";
            if (model != null)
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    using (var trans = dbCon.BeginTransaction())
                    {
                        try
                        {
                            dbCon.Execute(strSql, model, trans);
                            result.message = "更新蔬菜基本情况数据成功。";
                            result.status = DefaultKey.SuccessResult;
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            result.message = "更新蔬菜基本情况异常,异常信息:" + ex.ToString().Trim();
                            result.status = DefaultKey.ErrorResult;
                            LogHelper.WriteLog("更新蔬菜基本情况异常,异常信息:" + ex.ToString().Trim());
                            trans.Rollback();
                        }
                    }
                }
            }
            return result;
        }
        /// <summary>
        /// 判定是否生成过报表
        /// </summary>
        /// <param name="reportId"></param>
        /// <returns>没生成过true,否则false</returns>
        public bool CheckRepCount(string reportId)
        {
            string strSql = @"select count(1) from dbo.T_VegTecPromotion where ReportId=@ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportId = reportId }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("更新表1蔬菜生产实体情况表T_VegTecPromotion对象信息失败", ex);
                return false;
            }
        }

        /// <summary>
        /// 获取指定对象
        /// </summary>
        public override ResResult<VegTecPromotionEntity> GetModel(string ReportId)
        {
            ResResult<VegTecPromotionEntity> obj = new ResResult<VegTecPromotionEntity>();
            VegTecPromotionEntity model = new VegTecPromotionEntity();
            if (CheckRepCount(ReportId))
            {
                model.ReportId = ReportId;
                if (AddInfo(model).status > 0)
                {
                    obj.model = model;
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                }
            }
            else
            {
                string strSql = @"select * from dbo.T_VegTecPromotion where ReportId = @ReportId";
                try
                {
                    using (var dbCon = ConnectFactory.GetOpenConnection())
                    {
                        obj.model = dbCon.Query<VegTecPromotionEntity>(strSql, new { ReportId = ReportId }).FirstOrDefault();
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "获取成功！";
                        dbCon.Dispose();//数据库连接释放
                    }
                }
                catch (Exception ex)
                {
                    obj.status = DefaultKey.ErrorResult;
                    obj.message = ex.ToString().Trim();
                    LogHelper.ErrorLog("获取表1蔬菜生产实体情况调查T_VegTecPromotion对象信息失败", ex);
                }
            }
            return obj;
        }


        /// <summary>
        /// 获取今往年数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<VegTecPromotionViewModel> GetModel(VegNowModel model)
        {
            ResResult<VegTecPromotionViewModel> obj = new ResResult<VegTecPromotionViewModel>();
            VegTecPromotionEntity Entity = new VegTecPromotionEntity();
            VegTecPromotionViewModel viewModel = new VegTecPromotionViewModel();
            var paramlist = new DynamicParameters();
            string strSqlReport = "";
            if (model != null)
            {
                if (!string.IsNullOrEmpty(model.AreaCode) && !string.IsNullOrEmpty(model.ReportCode) && !string.IsNullOrEmpty(model.FillInYear))
                {
                    paramlist.Add("AreaCode", model.AreaCode);
                    paramlist.Add("ReportCode", model.ReportCode);
                    paramlist.Add("FillInYear", model.FillInYear);
                    int OldFillInYear = Convert.ToInt32(model.FillInYear) - 1;
                    if (OldFillInYear < 0)
                    {
                        OldFillInYear = 0;
                    }
                    paramlist.Add("OldFillInYear", OldFillInYear.ToString());
                    string strNowSql = "SELECT vf.Id,vf.ReportId,InstituNum,InstituNum_City,InstituNum_Area,InstituNum_town,PeopleNum,PeopleNum_City,PeopleNum_Area,PeopleNum_man,PeopleNum_woman,PeopleNum_age1,PeopleNum_age2"
                                    + ",PeopleNum_age3,PeopleNum_age4,PeopleNum_xlss,PeopleNum_xlbk,PeopleNum_xldz,PeopleNum_xlzz,PeopleNum_xlqt,PeopleNum_zczgj,PeopleNum_zcfgj,PeopleNum_zczj,PeopleNum_zccj,PeopleNum_zcqt"
                                    + ",vf.AddUser,vf.AddTime,vf.UpdateUser,vf.UpdateTime,ReportCode,Status as ReportStatus  FROM T_Report r LEFT JOIN T_VegTecPromotion vf on vf.ReportId = r.Id WHERE ReportCode = @ReportCode and FillInYear = @FillInYear and AreaCode = @AreaCode ";
                    string strOldSql = "SELECT vf.Id OldId,vf.ReportId OldReportId,InstituNum as OldInstituNum,InstituNum_City as OldInstituNum_City,InstituNum_Area as OldInstituNum_Area,InstituNum_town as OldInstituNum_town,PeopleNum as OldPeopleNum"
                                    + ",PeopleNum_City as OldPeopleNum_City,PeopleNum_Area as OldPeopleNum_Area,PeopleNum_man as OldPeopleNum_man,PeopleNum_woman as OldPeopleNum_woman,PeopleNum_age1 as OldPeopleNum_age1,PeopleNum_age2 as OldPeopleNum_age2"
                                    + ",PeopleNum_age3 as OldPeopleNum_age3,PeopleNum_age4 as OldPeopleNum_age4,PeopleNum_xlss as OldPeopleNum_xlss,PeopleNum_xlbk as OldPeopleNum_xlbk,PeopleNum_xldz as OldPeopleNum_xldz,PeopleNum_xlzz as OldPeopleNum_xlzz"
                                    + ",PeopleNum_xlqt as OldPeopleNum_xlqt,PeopleNum_zczgj as OldPeopleNum_zczgj,PeopleNum_zcfgj as OldPeopleNum_zcfgj,PeopleNum_zczj as OldPeopleNum_zczj,PeopleNum_zccj as OldPeopleNum_zccj,PeopleNum_zcqt as OldPeopleNum_zcqt"
                                    + ",vf.AddUser as OldAddUser,vf.AddTime as OldAddTime,vf.UpdateUser as OldUpdateUser,vf.UpdateTime as OldUpdateTime,ReportCode,Status as OldReportStatus  FROM T_Report r LEFT JOIN T_VegTecPromotion vf on vf.ReportId = r.Id WHERE ReportCode = @ReportCode and FillInYear = @OldFillInYear and AreaCode = @AreaCode ";
                    strSqlReport = "SELECT * FROM ((" + strNowSql + ")t LEFT JOIN  (" + strOldSql + ")tt ON t.ReportCode = tt.ReportCode)";

                    //判断选择的年度与上一年度是否生成过报表
                    if (CheckRepCreate(model.ReportCode, model.AreaCode, model.FillInYear) && CheckRepCreate(model.ReportCode, model.AreaCode, OldFillInYear.ToString()))
                    {
                        obj.model = viewModel;
                        obj.status = -1;
                        DictServices dictServices = new DictServices();
                        obj.message = dictServices.GetModel("ReportYear", model.FillInYear).model.DictText + "的报表无需填报！！";
                        return obj;
                    }
                    //判断本年是否生成过报表
                    else if (CheckRepCreate(model.ReportCode, model.AreaCode, model.FillInYear))
                    {
                        var dbCon = ConnectFactory.GetOpenConnection();
                        obj.model = dbCon.Query<VegTecPromotionViewModel>(strSqlReport, paramlist).FirstOrDefault();
                        obj.status = -2;
                        DictServices dictServices = new DictServices();
                        obj.message = dictServices.GetModel("ReportYear", model.FillInYear).model.DictText + "的报表无需填报！！";
                        dbCon.Dispose();//数据库连接释放
                        return obj;
                    }
                    //判断上一年是否生成过报表
                    //else if (CheckRepCreate(model.ReportCode, model.AreaCode, OldFillInYear.ToString()))
                    //{
                    //    var dbCon = ConnectFactory.GetOpenConnection();
                    //    obj.model = dbCon.Query<VegTecPromotionViewModel>(strNowSql, paramlist).FirstOrDefault();
                    //    obj.status = -3;
                    //    obj.message = "上一年的报表没有填报数据！！";
                    //    dbCon.Dispose();//数据库连接释放
                    //    return obj;
                    //}
                    else
                    {
                        //获取报表Id
                        Entity.ReportId = GetReportId(model.ReportCode, model.AreaCode, model.FillInYear);
                        //判断表T_VegFacility是否初始化过
                        if (CheckRepCount(Entity.ReportId))
                        {
                            if (AddInfo(Entity).status > 0)//报表初始化
                            {
                                using (var dbCon = ConnectFactory.GetOpenConnection())
                                {
                                    //查询去年数据
                                    obj.model = dbCon.Query<VegTecPromotionViewModel>(strSqlReport, paramlist).FirstOrDefault();
                                    obj.status = 1;//初始化成功status为1
                                    obj.message = "T_VegTecPromotion报表初始化成功！";
                                    dbCon.Dispose();//数据库连接释放
                                }
                            }
                        }
                        else
                        {
                            try
                            {
                                using (var dbCon = ConnectFactory.GetOpenConnection())
                                {
                                    obj.model = dbCon.Query<VegTecPromotionViewModel>(strSqlReport, paramlist).FirstOrDefault();
                                    obj.status = 2;//成功状态为2
                                    obj.message = "获取T_VegTecPromotion报表今往年数据成功！";
                                    dbCon.Dispose();//数据库连接释放
                                }
                            }
                            catch (Exception ex)
                            {
                                obj.status = 0; //失败状态
                                obj.message = ex.ToString().Trim();
                                LogHelper.ErrorLog("获取T_VegTecPromotion报表今往年数据失败", ex);
                            }
                        }
                    }
                }
                else
                {
                    obj.message = "当前地区没有对应报表";
                    obj.status = 0;
                }
            }
            else
            {
                obj.message = "参数错误,请与管理员联系!!!";
                obj.status = 0;
            }
            return obj;
        }
        /// <summary>
        /// 根据地区、报表代码、填报年度判断是否生成过报表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool CheckRepCreate(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select count(1) from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //为0，没生成过true,否则false
                    return dbCon.Query<int>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault() == 0 ? true : false;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表T_VegTecPromotion没有生成过", ex);
                return false;
            }
        }


        /// <summary>
        /// 根据地区、报表代码、填报年度获取报表Id
        /// </summary>
        /// <param name="ReportCode"></param>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public string GetReportId(string ReportCode, string AreaCode, string FillInYear)
        {
            string strSql = @"select * from dbo.T_Report where ReportCode = @ReportCode and AreaCode = @AreaCode and FillInYear = @FillInYear ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    return dbCon.Query<ReportEntity>(strSql, new { ReportCode = ReportCode, AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault().Id;
                }
            }
            catch (Exception ex)
            {
                LogHelper.ErrorLog("当前填报年度的表T_VegTecPromotion没有生成过", ex);
                return "0";
            }
        }


        /// <summary>
        /// 读取导入的Excel数据
        /// </summary>
        /// <param name="pathName">导入文件路径</param>
        /// <param name="fileSuffix">文件后缀</param>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="startRow">从第几行开始读取数据</param>
        /// <returns></returns>
        public List<VegTecPromotionModel> ExcelToModel(string pathName, string fileSuffix, string sheetName, int startRow)
        {

            IWorkbook workbook = null;
            ISheet sheet = null;
            List<VegTecPromotionModel> list = new List<VegTecPromotionModel>();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
            }
            else
            {
                sheet = workbook.GetSheetAt(0);
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(3);//从第四行开始读
                int cellCount = firstRow.LastCellNum;//获取列数

                //获取有数据的行数
                int rowCount = 0;
                for (int i = startRow; i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        rowCount = sheet.GetRow(i).RowNum;
                    }
                    else
                    {
                        break;
                    }
                }
                VegTecPromotionModel model = new VegTecPromotionModel();
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    //if (i == 3)//填报年度
                    //{
                    //    model.year = row.GetCell(2).ToString();
                    //}
                    if (i == 3)//蔬菜技术服务机构数
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.InstituNum = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.InstituNum = null;
                        }
                    }
                    if (i == 4)//州市级
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.InstituNum_City = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.InstituNum_City = null;
                        }
                    }
                    if (i == 5)//县级
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.InstituNum_Area = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.InstituNum_Area = null;
                        }
                    }
                    if (i == 6)//乡镇级
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.InstituNum_town = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.InstituNum_town = null;
                        }
                    }
                    if (i == 7)//专业技术人员数量
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum = null;
                        }
                    }
                    if (i == 8)//州市级人数
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_City = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_City = null;
                        }
                    }
                    if (i == 9)//县乡级人数
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_Area = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_Area = null;
                        }
                    }
                    if (i == 10)//男性人数
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_man = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_man = null;
                        }
                    }
                    if (i == 11)//女性人数
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_woman = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_woman = null;
                        }
                    }
                    if (i == 13)//30岁以下
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_age1 = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_age1 = null;
                        }
                    }
                    if (i == 14)//30-40岁（含40）
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_age2 = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_age2 = null;
                        }
                    }
                    if (i == 15)// 40-50岁（含50）
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_age3 = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_age3 = null;
                        }
                    }
                    if (i == 16)//50岁以上
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_age4 = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_age4 = null;
                        }
                    }
                    if (i == 18)//硕士及以上
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_xlss = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_xlss = null;
                        }
                    }
                    if (i == 19)//本科
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_xlbk = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_xlbk = null;
                        }
                    }
                    if (i == 20)//大专
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_xldz = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_xldz = null;
                        }
                    }
                    if (i == 21)//中专(技校)
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_xlzz = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_xlzz = null;
                        }
                    }
                    if (i == 22)//其他
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_xlqt = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_xlqt = null;
                        }
                    }
                    if (i == 24)//正高级职称
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_zczgj = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_zczgj = null;
                        }
                    }
                    if (i == 25)//副正高级职称
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_zcfgj = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_zcfgj = null;
                        }
                    }
                    if (i == 26)//中级职称
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_zczj = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_zczj = null;
                        }
                    }
                    if (i == 27)//初级职称
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_zccj = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_zccj = null;
                        }
                    }
                    if (i == 28)//其他
                    {
                        if (row.GetCell(2) != null && row.GetCell(2).ToString() != "")
                        {
                            model.PeopleNum_zcqt = int.Parse(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PeopleNum_zcqt = null;
                        }
                    }                   
                }
                list.Add(model);
            }
            return list;
        }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public MemoryStream Method(VegNowModel model)
        {
            model.FillInYear = dict.GetKey("ReportYear", model.FillInYear);
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("VegTecPromotion") as HSSFSheet;//读取模板所在sheet位置
            VegTecPromotionViewModel pageModel = GetModel(model).model;//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式
            HSSFRow datarow2 = (HSSFRow)sheet.GetRow(3);
            datarow2.GetCell(2).SetCellValue(pageModel.InstituNum == null ? "" : Common.GetADecimal(pageModel.InstituNum));
            HSSFRow datarow3 = (HSSFRow)sheet.GetRow(4);
            datarow3.GetCell(2).SetCellValue(pageModel.InstituNum_City == null ? "" : Common.GetADecimal(pageModel.InstituNum_City));
            HSSFRow datarow4 = (HSSFRow)sheet.GetRow(5);
            datarow4.GetCell(2).SetCellValue(pageModel.InstituNum_Area == null ? "" : Common.GetADecimal(pageModel.InstituNum_Area));
            HSSFRow datarow5 = (HSSFRow)sheet.GetRow(6);
            datarow5.GetCell(2).SetCellValue(pageModel.InstituNum_town == null ? "" : Common.GetADecimal(pageModel.InstituNum_town));
            HSSFRow datarow6 = (HSSFRow)sheet.GetRow(7);
            datarow6.GetCell(2).SetCellValue(pageModel.PeopleNum == null ? "" : Common.GetADecimal(pageModel.PeopleNum));
            HSSFRow datarow7 = (HSSFRow)sheet.GetRow(8);
            datarow7.GetCell(2).SetCellValue(pageModel.PeopleNum_City == null ? "" : Common.GetADecimal(pageModel.PeopleNum_City));
            HSSFRow datarow8 = (HSSFRow)sheet.GetRow(9);
            datarow8.GetCell(2).SetCellValue(pageModel.PeopleNum_Area == null ? "" : Common.GetADecimal(pageModel.PeopleNum_Area));
            HSSFRow datarow9 = (HSSFRow)sheet.GetRow(10);
            datarow9.GetCell(2).SetCellValue(pageModel.PeopleNum_man == null ? "" : Common.GetADecimal(pageModel.PeopleNum_man));
            HSSFRow datarow10 = (HSSFRow)sheet.GetRow(11);
            datarow10.GetCell(2).SetCellValue(pageModel.PeopleNum_woman == null ? "" : Common.GetADecimal(pageModel.PeopleNum_woman));
            HSSFRow datarow11 = (HSSFRow)sheet.GetRow(13);
            datarow11.GetCell(2).SetCellValue(pageModel.PeopleNum_age1 == null ? "" : Common.GetADecimal(pageModel.PeopleNum_age1));
            HSSFRow datarow12 = (HSSFRow)sheet.GetRow(14);
            datarow12.GetCell(2).SetCellValue(pageModel.PeopleNum_age2 == null ? "" : Common.GetADecimal(pageModel.PeopleNum_age2));
            HSSFRow datarow13 = (HSSFRow)sheet.GetRow(15);
            datarow13.GetCell(2).SetCellValue(pageModel.PeopleNum_age3 == null ? "" : Common.GetADecimal(pageModel.PeopleNum_age3));
            HSSFRow datarow14 = (HSSFRow)sheet.GetRow(16);
            datarow14.GetCell(2).SetCellValue(pageModel.PeopleNum_age4 == null ? "" : Common.GetADecimal(pageModel.PeopleNum_age4));
            HSSFRow datarow15 = (HSSFRow)sheet.GetRow(18);
            datarow15.GetCell(2).SetCellValue(pageModel.PeopleNum_xlss == null ? "" : Common.GetADecimal(pageModel.PeopleNum_xlss));
            HSSFRow datarow16 = (HSSFRow)sheet.GetRow(19);
            datarow16.GetCell(2).SetCellValue(pageModel.PeopleNum_xlbk == null ? "" : Common.GetADecimal(pageModel.PeopleNum_xlbk));
            HSSFRow datarow17 = (HSSFRow)sheet.GetRow(20);
            datarow17.GetCell(2).SetCellValue(pageModel.PeopleNum_xldz == null ? "" : Common.GetADecimal(pageModel.PeopleNum_xldz));
            HSSFRow datarow18 = (HSSFRow)sheet.GetRow(21);
            datarow18.GetCell(2).SetCellValue(pageModel.PeopleNum_xlzz == null ? "" : Common.GetADecimal(pageModel.PeopleNum_xlzz));
            HSSFRow datarow19 = (HSSFRow)sheet.GetRow(22);
            datarow19.GetCell(2).SetCellValue(pageModel.PeopleNum_xlqt == null ? "" : Common.GetADecimal(pageModel.PeopleNum_xlqt));
            HSSFRow datarow20 = (HSSFRow)sheet.GetRow(24);
            datarow20.GetCell(2).SetCellValue(pageModel.PeopleNum_zczgj == null ? "" : Common.GetADecimal(pageModel.PeopleNum_zczgj));
            HSSFRow datarow21 = (HSSFRow)sheet.GetRow(25);
            datarow21.GetCell(2).SetCellValue(pageModel.PeopleNum_zcfgj == null ? "" : Common.GetADecimal(pageModel.PeopleNum_zcfgj));
            HSSFRow datarow22 = (HSSFRow)sheet.GetRow(26);
            datarow22.GetCell(2).SetCellValue(pageModel.PeopleNum_zczj == null ? "" : Common.GetADecimal(pageModel.PeopleNum_zczj));
            HSSFRow datarow23 = (HSSFRow)sheet.GetRow(27);
            datarow23.GetCell(2).SetCellValue(pageModel.PeopleNum_zccj == null ? "" : Common.GetADecimal(pageModel.PeopleNum_zccj));
            HSSFRow datarow24 = (HSSFRow)sheet.GetRow(28);
            datarow24.GetCell(2).SetCellValue(pageModel.PeopleNum_zcqt == null ? "" : Common.GetADecimal(pageModel.PeopleNum_zcqt));

            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        public virtual ResResult<int> AddInfo(VegTecPromotionModel model)
        {
            model.AddTime = DateTime.Now;
            ResResult<int> obj = new ResResult<int>();
            string strSql = @"INSERT INTO dbo.T_VegTecPromotion";
            strSql += "(";
            strSql += "Id,";
            strSql += "ReportId,";
            strSql += "InstituNum,";
            strSql += "InstituNum_City,";
            strSql += "InstituNum_Area,";
            strSql += "InstituNum_town,";
            strSql += "PeopleNum,";
            strSql += "PeopleNum_man,";
            strSql += "PeopleNum_woman,";
            strSql += "PeopleNum_age1,";
            strSql += "PeopleNum_age2,";
            strSql += "PeopleNum_age3,";
            strSql += "PeopleNum_age4,";
            strSql += "PeopleNum_xlss,";
            strSql += "PeopleNum_xlbk,";
            strSql += "PeopleNum_xldz,";
            strSql += "PeopleNum_xlzz,";
            strSql += "PeopleNum_xlqt,";
            strSql += "PeopleNum_zczgj,";
            strSql += "PeopleNum_zcfgj,";
            strSql += "PeopleNum_zczj,";
            strSql += "PeopleNum_zccj,";
            strSql += "PeopleNum_zcqt,";
            strSql += "AddUser,";
            strSql += "AddTime,";
            strSql += "UpdateUser,";
            strSql += "UpdateTime)";
            strSql += " values(";
            strSql += "@Id,";
            strSql += "@ReportId,";
            strSql += "@InstituNum,";
            strSql += "@InstituNum_City,";
            strSql += "@InstituNum_Area,";
            strSql += "@InstituNum_town,";
            strSql += "@PeopleNum,";
            strSql += "@PeopleNum_man,";
            strSql += "@PeopleNum_woman,";
            strSql += "@PeopleNum_age1,";
            strSql += "@PeopleNum_age2,";
            strSql += "@PeopleNum_age3,";
            strSql += "@PeopleNum_age4,";
            strSql += "@PeopleNum_xlss,";
            strSql += "@PeopleNum_xlbk,";
            strSql += "@PeopleNum_xldz,";
            strSql += "@PeopleNum_xlzz,";
            strSql += "@PeopleNum_xlqt,";
            strSql += "@PeopleNum_zczgj,";
            strSql += "@PeopleNum_zcfgj,";
            strSql += "@PeopleNum_zczj,";
            strSql += "@PeopleNum_zccj,";
            strSql += "@PeopleNum_zcqt,";
            strSql += "@AddUser,";
            strSql += "@AddTime,";
            strSql += "@UpdateUser,";
            strSql += "@UpdateTime)";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model.Id = CustomFun.GetGuid();
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "添加成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "添加失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("新增蔬菜技术推广人员情况表T_VegTecPromotion对象信息失败", ex);
                return obj;
            }
        }

        /// <summary>
        /// 修改\更新
        /// </summary>
        public virtual ResResult<int> UpdateInfo(VegTecPromotionModel model)
        {
            ResResult<int> obj = new ResResult<int>();
            ReportServices reportServices = new ReportServices();
            string strSql = @"UPDATE dbo.T_VegTecPromotion set ";
            strSql += "InstituNum = @InstituNum,";
            strSql += "InstituNum_City = @InstituNum_City,";
            strSql += "InstituNum_Area = @InstituNum_Area,";
            strSql += "InstituNum_town = @InstituNum_town,";
            strSql += "PeopleNum = @PeopleNum,";
            strSql += "PeopleNum_City = @PeopleNum_City,";
            strSql += "PeopleNum_Area = @PeopleNum_Area,";
            strSql += "PeopleNum_man = @PeopleNum_man,";
            strSql += "PeopleNum_woman = @PeopleNum_woman,";
            strSql += "PeopleNum_age1 = @PeopleNum_age1,";
            strSql += "PeopleNum_age2 = @PeopleNum_age2,";
            strSql += "PeopleNum_age3 = @PeopleNum_age3,";
            strSql += "PeopleNum_age4 = @PeopleNum_age4,";
            strSql += "PeopleNum_xlss = @PeopleNum_xlss,";
            strSql += "PeopleNum_xlbk = @PeopleNum_xlbk,";
            strSql += "PeopleNum_xldz = @PeopleNum_xldz,";
            strSql += "PeopleNum_xlzz = @PeopleNum_xlzz,";
            strSql += "PeopleNum_xlqt = @PeopleNum_xlqt,";
            strSql += "PeopleNum_zczgj = @PeopleNum_zczgj,";
            strSql += "PeopleNum_zcfgj = @PeopleNum_zcfgj,";
            strSql += "PeopleNum_zczj = @PeopleNum_zczj,";
            strSql += "PeopleNum_zccj = @PeopleNum_zccj,";
            strSql += "PeopleNum_zcqt = @PeopleNum_zcqt,";
            strSql += "AddUser = @AddUser,";
            strSql += "AddTime = @AddTime,";
            strSql += "UpdateUser = @UpdateUser,";
            strSql += "UpdateTime = @UpdateTime";
            strSql += " where ReportId = @ReportId";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    int result = dbCon.Execute(strSql, model);
                    if (result > 0)
                    {
                        obj.model = result;
                        obj.status = DefaultKey.SuccessResult;
                        obj.message = "数据更新成功！";
                    }
                    else
                    {
                        obj.model = result;
                        obj.status = DefaultKey.InfoResult;
                        obj.message = "更新失败！数量为:" + result;
                    }
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("更新表4蔬菜外销调查表T_VegExport对象信息失败", ex);
            }
            return obj;

        }
        /// <summary>
        /// 获取统计数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<VegTecPromotionStatsModel>> GetStatsList(VegNowModel model)
        {
            DictServices dictServices = new DictServices();
            ResResult<List<VegTecPromotionStatsModel>> result = new ResResult<List<VegTecPromotionStatsModel>>();
            var paramlist = new DynamicParameters();
            int OldFillInYear = 0;
            if (!string.IsNullOrEmpty(model.AreaCode))
            {
                paramlist.Add("AreaCode", model.AreaCode);
            }
            if (!string.IsNullOrEmpty(model.FillInYear))
            {
                model.FillInYear = dictServices.GetKey("ReportYear", model.FillInYear); 
                OldFillInYear = Convert.ToInt32(model.FillInYear) - 1;//上一年
                if (OldFillInYear < 0)
                {
                    OldFillInYear = 0;
                }
                paramlist.Add("FillInYear", model.FillInYear);
                paramlist.Add("OldFillInYear", OldFillInYear);
            }
            if (!string.IsNullOrEmpty(model.Status))
            {
                paramlist.Add("Status", model.Status);
            }
            #region 当年统计数据
            string strNowSql = "select Area.Code AreaCode,Dict.DictText FillYear, FullName AreaName,Coord,";
            strNowSql += "ISNULL(InstituNum_City,0.00)+ISNULL(InstituNum_Area,0.00)+ISNULL(InstituNum_town,0.00)  InstituNum,"; //         --蔬菜技术服务机构数（个）
            strNowSql += "ISNULL(InstituNum_City,0.00)   InstituNum_City,        "; //           --州市级
            strNowSql += "ISNULL(InstituNum_Area,0.00)   InstituNum_Area,         "; //          --县级
            strNowSql += "ISNULL(InstituNum_town,0.00)   InstituNum_town,        "; // --乡镇级        "; //
            strNowSql += "ISNULL(PeopleNum_City,0.00)+ISNULL(PeopleNum_Area,0.00) PeopleNum    ,        "; // --二、专业技术人员数量（人）
            strNowSql += "ISNULL(PeopleNum_City,0.00)  PeopleNum_City,         "; // --州市级人数
            strNowSql += "ISNULL(PeopleNum_Area,0.00) PeopleNum_Area ,         "; //--县乡级人数
            strNowSql += "ISNULL(PeopleNum_man,0.00) PeopleNum_man ,        "; // --男
            strNowSql += "ISNULL(PeopleNum_woman,0.00) PeopleNum_woman ,        "; // --女
            strNowSql += "ISNULL(PeopleNum_age1,0.00) PeopleNum_age1 ,         "; //--30岁以下（含30）
            strNowSql += "ISNULL(PeopleNum_age2,0.00) PeopleNum_age2 ,         "; //--30-40岁（含40）
            strNowSql += "ISNULL(PeopleNum_age3,0.00) PeopleNum_age3 ,        "; // --40-50岁（含50）
            strNowSql += "ISNULL(PeopleNum_age4,0.00) PeopleNum_age4 ,         "; //--50岁以上
            strNowSql += "ISNULL(PeopleNum_xlss,0.00) PeopleNum_xlss ,         "; //--硕士及以上
            strNowSql += "ISNULL(PeopleNum_xlbk,0.00) PeopleNum_xlbk ,        "; // --本科
            strNowSql += "ISNULL(PeopleNum_xldz,0.00) PeopleNum_xldz ,        "; // --大专
            strNowSql += "ISNULL(PeopleNum_xlzz,0.00) PeopleNum_xlzz ,         "; //--中专
            strNowSql += "ISNULL(PeopleNum_xlqt,0.00) PeopleNum_xlqt ,        "; // --其他
            strNowSql += "ISNULL(PeopleNum_zczgj,0.00) PeopleNum_zczgj ,        "; // --正高级职称
            strNowSql += "ISNULL(PeopleNum_zcfgj,0.00) PeopleNum_zcfgj ,        "; // --副高级职称
            strNowSql += "ISNULL(PeopleNum_zczj,0.00) PeopleNum_zczj ,        "; // --中级职称
            strNowSql += "ISNULL(PeopleNum_zccj,0.00) PeopleNum_zccj ,        "; // --初级职称
            strNowSql += "ISNULL(PeopleNum_zcqt,0.00) PeopleNum_zcqt        "; //  --其他
            strNowSql += "from(    ";
            strNowSql += "select     ";
            if (model.AreaCode == DefaultKey.ProvinceCode || string.IsNullOrEmpty(model.AreaCode))
            {
                strNowSql += "case when grouping(Area.ParentCode) = 1 then @AreaCode else ParentCode end ParentCode,    ";
            }
            else
            {
                strNowSql += "case when grouping(Area.Code) = 1 then @AreaCode else Code end Code,    ";
            }
            //strNowSql += "SUM(ISNULL(VegTecPromotion.InstituNum,0.00))  InstituNum,        "; //       --蔬菜技术服务机构数（个）
            strNowSql += "SUM(ISNULL(VegTecPromotion.InstituNum_City,0.00))   InstituNum_City,     "; //           --州市级
            strNowSql += "SUM(ISNULL(VegTecPromotion.InstituNum_Area,0.00))   InstituNum_Area,     "; //           --县级
            strNowSql += "SUM(ISNULL(VegTecPromotion.InstituNum_town,0.00))   InstituNum_town,     "; // --乡镇级
            //strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum,0.00)) PeopleNum ,     "; // --二、专业技术人员数量（人）
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_City,0.00))  PeopleNum_City,     "; //  --州市级人数
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_Area,0.00)) PeopleNum_Area ,     "; // --县乡级人数
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_man,0.00)) PeopleNum_man ,     "; // --男
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_woman,0.00)) PeopleNum_woman ,     "; // --女
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_age1,0.00)) PeopleNum_age1 ,     "; // --30岁以下（含30）
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_age2,0.00)) PeopleNum_age2 ,     "; // --30-40岁（含40）
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_age3,0.00)) PeopleNum_age3 ,     "; // --40-50岁（含50）
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_age4,0.00)) PeopleNum_age4 ,     "; // --50岁以上
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_xlss,0.00)) PeopleNum_xlss ,     "; // --硕士及以上
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_xlbk,0.00)) PeopleNum_xlbk ,     "; // --本科
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_xldz,0.00)) PeopleNum_xldz ,     "; // --大专
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_xlzz,0.00)) PeopleNum_xlzz ,     "; // --中专
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_xlqt,0.00)) PeopleNum_xlqt ,     "; // --其他
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_zczgj,0.00)) PeopleNum_zczgj ,     "; // --正高级职称
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_zcfgj,0.00)) PeopleNum_zcfgj ,     "; // --副高级职称
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_zczj,0.00)) PeopleNum_zczj ,     "; // --中级职称
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_zccj,0.00)) PeopleNum_zccj ,     "; // --初级职称
            strNowSql += "SUM(ISNULL(VegTecPromotion.PeopleNum_zcqt,0.00)) PeopleNum_zcqt     "; //  --其他
            strNowSql += "from[dbo].[T_Report] Report    ";
            strNowSql += "left join [dbo].[T_VegTecPromotion] VegTecPromotion on Report.Id=VegTecPromotion.ReportId      ";
            strNowSql += "right join T_Area Area on Area.Code = Report.AreaCode    ";
            strNowSql += "where    ";
            if (model.AreaCode == DefaultKey.ProvinceCode || model.AreaCode == "")
            {
                strNowSql += "Area.ParentCode! = @AreaCode    ";
            }
            else
            {
                strNowSql += "Area.ParentCode = @AreaCode  or Area.Code=@AreaCode   ";
            }
            strNowSql += "and Report.FillInYear = @FillInYear    ";
            strNowSql += "and Report.Status = @Status     "; //--表示审核通过
            if (model.AreaCode == DefaultKey.ProvinceCode || model.AreaCode == "")
            {
                strNowSql += "group by rollup(Area.ParentCode)    ";
            }
            else
            {
                strNowSql += "group by rollup(Area.Code)    ";
            }
            strNowSql += ") tt1    ";
            if (model.AreaCode == DefaultKey.ProvinceCode || model.AreaCode == "")
            {
                strNowSql += "right join T_Area  Area on Area.Code=tt1.ParentCode       ";
            }
            else
            {
                strNowSql += "right join T_Area Area  on tt1.Code=Area.Code   ";
            }
            strNowSql += "inner join T_Dict Dict  on Dict.GroupCode = 'ReportYear' and DictValue = @FillInYear    ";
            strNowSql += "where Area.ParentCode = @AreaCode  or Area.Code = @AreaCode    order by Area.Code ";
            #endregion
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    //查询去年数据
                    result.model = dbCon.Query<VegTecPromotionStatsModel>(strNowSql, paramlist).ToList();
                    result.status = DefaultKey.SuccessResult;//初始化成功status为1
                    result.message = "VegTecPromotion报表统计数据获取成功！";
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                result.status = DefaultKey.ErrorResult;
                result.message = "VegTecPromotion报表统计数据获取失败" + ex.ToString().Trim();
            }
            return result;
        }
        /// <summary>
        /// 获取统计详情
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResResult<List<VegTecPromotionStatsModel>> GetDetailStats(VegNowModel model)
        {
            ResResult<List<VegTecPromotionStatsModel>> res = new ResResult<List<VegTecPromotionStatsModel>>();
            List<VegTecPromotionStatsModel> list = new List<VegTecPromotionStatsModel>();
            string ProvinceCode = DefaultKey.ProvinceCode;
            string FillInYear = model.FillInYear;
            if (model.AreaCode == ProvinceCode)
            {
                var First = GetStatsList(model).model;
                First[0].AreaName = "合计";
                list.Add(First[0]);//省级汇总合计
                foreach (var item in First)
                {
                    if (item.AreaCode != ProvinceCode)
                    {
                        VegNowModel model2 = new VegNowModel();
                        model2.FillInYear = FillInYear;
                        model2.AreaCode = item.AreaCode;
                        model2.Status = model.Status;
                        var Second = GetStatsList(model2).model;
                        list.Add(item);
                        foreach (var item2 in Second)
                        {
                            if (model2.AreaCode != item2.AreaCode)
                            {
                                list.Add(item2);
                            }
                        }
                    }
                }
            }
            else
            {
                list = GetStatsList(model).model;//数据获取
            }
            if(list!=null)
            {
                res.model = list;
                res.message = "获取统计数据详情成功";
                res.status = DefaultKey.SuccessResult;
            }
            else
            {
                res.message = "获取统计数据详情成失败";
                res.status = DefaultKey.ErrorResult;
            }
            return res;
        }
        public MemoryStream MethodStats(VegNowModel model)
        {
            string FillInYear = model.FillInYear;
            AreaServices areaServices = new AreaServices();
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string AreaName = areaServices.GetByCodeModel(model.AreaCode).model.Name;//获取当前行政区划名称
            getExcelStatsList(workbook, model, AreaName);
            if (model.AreaCode == DefaultKey.ProvinceCode)//判断当前用户是否为省级用户
            {
                var areaList = areaServices.GetPrefectureList();//获取州市级列表
                foreach (var areaCode in areaList)
                {
                    model.AreaCode = areaCode;
                    model.FillInYear = FillInYear;
                    getExcelStatsList(workbook, model, areaServices.GetByCodeModel(areaCode).model.Name);
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }
        /// <summary>
        /// 返回excel工作簿
        /// </summary>
        /// <param name="workbook">工作簿</param>
        /// <param name="sheet">子表</param>
        /// <param name="model"></param>
        /// <param name="AreaName">地区名称</param>
        /// <returns></returns>
        public HSSFWorkbook getExcelStatsList(HSSFWorkbook workbook, VegNowModel model, string AreaName)
        {
            FileStream file = null;
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\VegStatsDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("VegTecPromotion") as HSSFSheet;//读取模板所在sheet位置
            List<VegTecPromotionStatsModel> list = new List<VegTecPromotionStatsModel>();
            list = null;
            list = GetDetailStats(model).model;
            HSSFRow row = (HSSFRow)sheet.GetRow(1);//读取模板第二行
            var style = row.GetCell(0).CellStyle;//读取模板第二行第一单元格样式并保存
            row.GetCell(0).CellStyle = style;//对第二行第一单元格设置原来样式
            if (list != null)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(i + 4);
                    style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                    if (i == 0)
                    {
                        list[0].AreaName = "合计";
                        //datarow.RowStyle = style;
                    }
                    datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName.ToString());//地区
                    datarow.CreateCell(1).SetCellValue(list[i].InstituNum == null ? "" : Common.GetADecimal(list[i].InstituNum));//常年蔬菜占地面积
                    datarow.CreateCell(2).SetCellValue(list[i].InstituNum_City == null ? "" : Common.GetADecimal(list[i].InstituNum_City));
                    datarow.CreateCell(3).SetCellValue(list[i].InstituNum_Area == null ? "" : Common.GetADecimal(list[i].InstituNum_Area));//冬春季蔬菜面积
                    datarow.CreateCell(4).SetCellValue(list[i].InstituNum_town == null ? "" : Common.GetADecimal(list[i].InstituNum_town));
                    datarow.CreateCell(5).SetCellValue(list[i].PeopleNum == null ? "" : Common.GetADecimal(list[i].PeopleNum));//春夏季蔬菜面积
                    datarow.CreateCell(6).SetCellValue(list[i].PeopleNum_City == null ? "" : Common.GetADecimal(list[i].PeopleNum_City));//
                    datarow.CreateCell(7).SetCellValue(list[i].PeopleNum_Area == null ? "" : Common.GetADecimal(list[i].PeopleNum_Area));//菜地喷滴灌面积
                    datarow.CreateCell(8).SetCellValue(list[i].PeopleNum_man == null ? "" : Common.GetADecimal(list[i].PeopleNum_man));//
                    datarow.CreateCell(9).SetCellValue(list[i].PeopleNum_woman == null ? "" : Common.GetADecimal(list[i].PeopleNum_woman));//
                    datarow.CreateCell(10).SetCellValue(list[i].PeopleNum_age1 == null ? "" : Common.GetADecimal(list[i].PeopleNum_age1));//
                    datarow.CreateCell(11).SetCellValue(list[i].PeopleNum_age2 == null ? "" : Common.GetADecimal(list[i].PeopleNum_age2));//
                    datarow.CreateCell(12).SetCellValue(list[i].PeopleNum_age3 == null ? "" : Common.GetADecimal(list[i].PeopleNum_age3));//
                    datarow.CreateCell(13).SetCellValue(list[i].PeopleNum_age4 == null ? "" : Common.GetADecimal(list[i].PeopleNum_age4));//
                    datarow.CreateCell(14).SetCellValue(list[i].PeopleNum_xlss == null ? "" : Common.GetADecimal(list[i].PeopleNum_xlss));//
                    datarow.CreateCell(15).SetCellValue(list[i].PeopleNum_xlbk == null ? "" : Common.GetADecimal(list[i].PeopleNum_xlbk));//
                    datarow.CreateCell(16).SetCellValue(list[i].PeopleNum_xldz == null ? "" : Common.GetADecimal(list[i].PeopleNum_xldz));//
                    datarow.CreateCell(17).SetCellValue(list[i].PeopleNum_xlzz == null ? "" : Common.GetADecimal(list[i].PeopleNum_xlzz));//
                    datarow.CreateCell(18).SetCellValue(list[i].PeopleNum_xlqt == null ? "" : Common.GetADecimal(list[i].PeopleNum_xlqt));//
                    datarow.CreateCell(19).SetCellValue(list[i].PeopleNum_zczgj == null ? "" : Common.GetADecimal(list[i].PeopleNum_zczgj));//
                    datarow.CreateCell(20).SetCellValue(list[i].PeopleNum_zcfgj == null ? "" : Common.GetADecimal(list[i].PeopleNum_zcfgj));//
                    datarow.CreateCell(21).SetCellValue(list[i].PeopleNum_zczj == null ? "" : Common.GetADecimal(list[i].PeopleNum_zczj));//
                    datarow.CreateCell(22).SetCellValue(list[i].PeopleNum_zccj == null ? "" : Common.GetADecimal(list[i].PeopleNum_zccj));//
                    datarow.CreateCell(23).SetCellValue(list[i].PeopleNum_zcqt == null ? "" : Common.GetADecimal(list[i].PeopleNum_zcqt));//
                    datarow.GetCell(0).CellStyle = style;
                }
                sheet.CopyTo(workbook, AreaName, true, true);
            }
            return workbook;
        }
    }
}